set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;

insert overwrite table jms_dm.dm_star_staff_rnk_sum_dt partition (dt)
select 
    network_code                                                              -- 网点code
    , network_name                                                            -- 网点名称
    , agent_code                                                              -- 代理区code
    , agent_name                                                              -- 代理区名称
    , case when date_type = '0' then null else date_type end                  -- 日期类型 1：月维度 2：季维度
    , count(staff_code)                                as staff_num           -- 排名人数
    , sum(case when star_level = 1 then 1 else 0 end)  as star_level1_num     -- 一星级人数
    , sum(case when star_level = 2 then 1 else 0 end)  as star_level2_num     -- 二星级人数
    , sum(case when star_level = 3 then 1 else 0 end)  as star_level3_num     -- 三星级人数
    , sum(case when star_level = 4 then 1 else 0 end)  as star_level4_num     -- 四星级人数
    , sum(case when star_level = 5 then 1 else 0 end)  as star_level5_num     -- 五星级人数
    , date_time                                                               -- 时间
    , dt
from 
    jms_dm.dm_star_staff_rnk_detail_dt
where 
    dt = '{{ execution_date | cst_ds }}'
    -- and date_type in ('1' , '2')
group by 
    network_code
    , network_name
    , agent_code
    , agent_name
    , date_type
    , date_time
    , dt

union all

select
      null as  network_code                                                   -- 网点code
    , null as  network_name                                                   -- 网点名称
    , agent_code                                                              -- 代理区code
    , agent_name                                                              -- 代理区名称
    , date_type                                                               -- 日期类型 1：月维度 2：季维度
    , count(staff_code)                                as staff_num           -- 排名人数
    , sum(case when star_level = 1 then 1 else 0 end)  as star_level1_num     -- 一星级人数
    , sum(case when star_level = 2 then 1 else 0 end)  as star_level2_num     -- 二星级人数
    , sum(case when star_level = 3 then 1 else 0 end)  as star_level3_num     -- 三星级人数
    , sum(case when star_level = 4 then 1 else 0 end)  as star_level4_num     -- 四星级人数
    , sum(case when star_level = 5 then 1 else 0 end)  as star_level5_num     -- 五星级人数
    , date_time                                                               -- 时间
    , dt
from
    jms_dm.dm_star_staff_rnk_detail_dt
where
    dt = '{{ execution_date | cst_ds }}'
    and date_type in ('1' , '2')
group by
    agent_code
    , agent_name
    , date_type
    , date_time
    , dt
distribute by 1
;